/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dbsucxentW;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Arrays;

/**
 *
 * @author zhouyong
 */
public class NonPrefetchingTest {

    public static String mergeSQL(String[] input) {
        String TempSQLWith = "";
        String TempSQL = "";
        int Counter = 1;
        for (int i = 0; i < input.length; i++) {
            String currentQuery = input[i].replaceAll("   ", " ").replaceAll("  ", " ");
            if (currentQuery.contains("WITH V")) {
                String[] temp = null;

                if (currentQuery.contains(") SELECT V")) {
                    temp = currentQuery.split("SELECT V.");
                } else if (currentQuery.contains(") SELECT A.leafValue")) {
                    temp = currentQuery.split("SELECT A.leafValue");
                }

                if (temp == null) {
                    //System.out.println("ERROR!");
                    return "ERROR!";
                }

                temp[1] = "SELECT V." + temp[1];

                temp[0] = temp[0].replace("WITH V", "WITH Z" + Counter);
                temp[1] = temp[1].replace("SELECT V.", "SELECT Z" + Counter + ".");
                temp[1] = temp[1].replace("FROM V", "FROM Z" + Counter);
                temp[1] = temp[1].replace("V.branchOrder", "Z" + Counter + ".branchOrder");
                temp[1] = temp[1].replace("V.DeweyOrderSum", "Z" + Counter + ".DeweyOrderSum");
                temp[1] = temp[1].replace("V.DocId", "Z" + Counter + ".DocId");
                temp[1] = temp[1].replace("V.LeafOrder", "Z" + Counter + ".LeafOrder");
                temp[1] = temp[1].replace("FROM Attribute A, V", "FROM Attribute A, Z" + Counter);
                temp[1] = temp[1].replace("ORDER BY DocId, DeweyOrderSum, Attr, AttrOrder", "");
                temp[1] = temp[1].replace("ORDER BY DocId, DeweyOrderSum, AttrOrder", "");
                temp[1] = temp[1].replace("OPTION (FORCE ORDER)", "");
                if (!TempSQLWith.equals("")) {
                    TempSQLWith += ", ";
                    temp[0] = temp[0].replace("WITH", "");
                }

                TempSQLWith += temp[0];
                if (!TempSQL.equals("")) {
                    TempSQL += " UNION ";
                }

                TempSQL += "( " + temp[1] + " ) ";

                Counter++;
            } else {
                currentQuery = currentQuery.replace("ORDER BY DocId, DeweyOrderSum", "");
                currentQuery = currentQuery.replace("OPTION (FORCE ORDER)", "");
                if (!TempSQL.equals("")) {
                    TempSQL += " UNION ";
                }
                TempSQL += "( " + currentQuery + " ) ";
            }

            if (i == (input.length - 1)) {
                TempSQL += "ORDER BY DocId, DeweyOrderSum ";
                TempSQL += "OPTION (FORCE ORDER) ";
            }
        }
        return TempSQLWith + " " + TempSQL;
    }

    public static void main(String[] args) {
        BufferedReader freader = null;
        PrintWriter fwriter = null;
        try {
            String[] FILE_LOCATIONS = new String[]{
                    "data/samplequeries.txt",
                    //"data/queries2.txt",
                    //"data/queries3.txt",
                    //"data/queries4.txt",
                    //"data/queries5.txt",
            };
            String[] RESULTS = new String[]{
                    "data/queries_result1.txt",
                    "data/queries_result2.txt",
                    "data/queries_result3.txt",
                    "data/queries_result4.txt",
                    "data/queries_result5.txt"};
            String USER = "sucxent";
            String PASS = "123";
            String DBNAME1 = "DC1GB";
            long THRESHOLD = 30 * 60 * 1000;
            //String DBNAME2 = "XBENCH1000MB";

            Sucxent db = new Sucxent();
            db.connect(USER, PASS, DBNAME1);
            for (int i = 0; i < FILE_LOCATIONS.length; i++) {
                String file = FILE_LOCATIONS[i];
                String result = RESULTS[i];
                freader = new BufferedReader(new FileReader(file));
                fwriter = new PrintWriter(new FileWriter(new File(result)));

                long t1 = System.currentTimeMillis();
                String query;
                int queryid = 1;
                while ((query = freader.readLine()) != null) {
                    String sql = null;
                    if (!"".equals(query.trim()) && !query.trim().startsWith("--")) {
                        String[] paths = query.split("\\|");
                        
                        long t2 = System.currentTimeMillis();
                        if (paths.length == 1) {
                            sql = db.translate(paths[0], false);
                        } else {
                            String[] sqls = new String[paths.length];
                            int index = 0;
                            for (String path : paths) {
                                System.out.println(path);
                                sqls[index++] = db.translate(path, false);
                            }
                            sql = mergeSQL(sqls);
                        }
                        long t3 = System.currentTimeMillis();
                        
                        fwriter.println("PATH " + (queryid++) + ": " + query);
                        fwriter.println("Timing: ");
                        fwriter.println("Translation Time: " + (t3 - t2) + " ms");
                        fwriter.flush();
                        
                        long[] delta = new long[6];
                        boolean isDNF = false;
                        Arrays.fill(delta, 0);
                        for (int j = 0; j < 6; j++) {
                            long t4 = System.currentTimeMillis();
                            //db.execute(sql);
                            long t5 = System.currentTimeMillis();
                            delta[j] = t5 - t4;
                            if (delta[0] > THRESHOLD && delta[1] > THRESHOLD) {
                                isDNF = true;
                                break;
                            }
                        }

                        if (isDNF) {
                            fwriter.println("Execution Time: " + "DNF");
                            fwriter.println("Execution Time Detail: " + Arrays.toString(delta));
                            fwriter.println("SQL Query:");
                            fwriter.println(sql);
                        } else {
                            long sum = 0;
                            for (int k=1; k<delta.length; k++) {
                                sum += delta[k];
                            }
                            fwriter.println("Execution Time: " + (sum / 5) + " ms");
                            fwriter.println("Execution Time Detail: " + Arrays.toString(delta));
                            fwriter.println("SQL Query:");
                            fwriter.println(sql);
                        }
                            
                        fwriter.println("\n\n----------------------------------------------------------------\n\n");
                        fwriter.flush();
                    }
                }
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                freader.close();
                fwriter.flush();
                fwriter.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }
}
